package com.atwisdom.star.common.util;

import com.atwisdom.star.exception.pojo.BusinessException;
import jodd.cache.TimedCache;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.FileInputStream;
import java.io.InputStream;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class EasyExcel{
    private static final Logger logger = LoggerFactory.getLogger(EasyExcel.class);

    private static final String CONFIG_SHEET_NAME = "参数配置";
    private static final String COLUMN_SHEET_NAME = "列定义";
    private static final String CONFIG_SHEET_ARGC_COL_NAME = "参数项";
    private static final String TEMPLATE_ROOT_PATH = "/com";
    private static final String BOOLEAN_TRUE_TEXT = "是";
    private static final String BOOLEAN_FALSE_TEXT = "否";
    private static final String ROW_SPLIT_FLAG = "#R";
    private static final String COL_SPLIT_FLAG = "#C";
    private static final String ENTITY_INST_NAME = "obj";
    private static final String EXPORT_TYPE_XLS = "xls";
    private static final String EXPORT_TYPE_ZIP = "zip";
    private static final String JAR_LAUNCH_FLAG = "!";
    private static final String SHEET_NAME_OF_SINGLE = "导出数据";
    private static TimedCache templateCache = new TimedCache(600000L);

    public EasyExcel() {
    }

    private static boolean isDateCell(Cell cell) {
        try {
            return HSSFDateUtil.isCellDateFormatted(cell);
        } catch (Exception e) {
            return false;
        }
    }



    //filFullName:文件名(含路径)
    private static Sheet readExcelSheet(InputStream inputStream,String extName,int index)  {
        try {
            Object wb = null;
            if (extName.equalsIgnoreCase(".xlsx")) {
                wb = new XSSFWorkbook(inputStream);
            } else if (extName.equalsIgnoreCase(".xls")) {
                wb = new HSSFWorkbook(inputStream);
            } else {
                throw new BusinessException("999999","文件格式不正确，仅支持xls和xlsx格式");
            }
            int sheetCount = ((Workbook) wb).getNumberOfSheets();
            if(sheetCount <= index) return null;
            return ((Workbook) wb).getSheetAt(index);
        }catch (Exception e){
            logger.error("error", e);
            throw new BusinessException("999999","文件流读取失败：" + e.getMessage());
        }
    }

    private static String[][] readSheetData(Sheet sheet,int rowStart,int colStart)  {
        //得到行列数
        int rowCount = sheet.getLastRowNum() +1 - rowStart;
        Row dataRow = sheet.getRow(rowStart);
        int colCount = dataRow.getLastCellNum() - colStart;
        List<String[]> res = new ArrayList<String[]>();

        //读取文件内容,含标题
        for(int i = 0; i < rowCount; i++) {
            dataRow = sheet.getRow(rowStart + i);
            String[] rowData = new String[colCount];
            boolean isBlank = true;
            for(int j = 0; j < colCount; j++) {
                if(dataRow != null) {
                    Cell cell = dataRow.getCell(colStart + j);
                    if (cell != null) {
                        String content = "";
                        if (isDateCell(cell)) {
                            try {
                                Date d = cell.getDateCellValue();
                                content = DateUtils.getDateFormat(d, "yyyy-MM-dd HH:mm:ss");
                            } catch (Exception e) {
                            }
                        } else {
                            cell.setCellType(CellType.STRING);
                            content = cell.getStringCellValue();
                            Pattern p = Pattern.compile("\t|\r|\n");
                            Matcher m = p.matcher(content);
                            content = m.replaceAll("");
                        }
                        rowData[j] = content.trim();
                    } else {
                        rowData[j] = "";
                    }
                }else{
                    rowData[j] = "";
                }
                if(!"".equalsIgnoreCase(rowData[j])){
                    isBlank = false;
                }
            }
            if(!isBlank) res.add(rowData);
        }
        return res.toArray(new String[0][0]);
    }


    /**
     * 读取Excel文件
     * @param   rowStart        自定义 起始行
     * @param  colStart         自定义 起始列
     */
    public static String[][] readExcelDataToArray(InputStream inputStream,String extName,int rowStart,int colStart)  {
        Sheet sheet = readExcelSheet(inputStream,extName,0);
        return readSheetData(sheet,rowStart,colStart);
    }
    public static String[][] readExcelDataToArray(InputStream inputStream,String extName,int sheetIndex,int rowStart,int colStart)  {
        Sheet sheet = readExcelSheet(inputStream,extName,sheetIndex);
        if(sheet == null) return null;
        return readSheetData(sheet,rowStart,colStart);
    }
    public static Map<Integer,String> readExcelSheetNames(String filFullName){
        try {
            Object wb = null;
            if (filFullName.endsWith(".xlsx")) {
                wb = new XSSFWorkbook(new FileInputStream(filFullName));
            } else if (filFullName.endsWith(".xls")) {
                wb = new HSSFWorkbook(new FileInputStream(filFullName));
            } else {
                throw new BusinessException("999999","文件格式不正确，仅支持xls和xlsx格式");
            }
            Map<Integer,String> sheetNames = new HashMap<>();
            int sheetCount = ((Workbook) wb).getNumberOfSheets();
            for(int i=0;i<sheetCount;i++){
                sheetNames.put(i,((Workbook) wb).getSheetName(i));
            }
            return sheetNames;
        }catch (Exception e){
            logger.error("error", e);
            throw new BusinessException("999999","文件流读取失败：" + e.getMessage());
        }
    }

    public static Map<Integer,String> readExcelSheetNames(InputStream inputStream,String extName){
        try {
            Object wb = null;
            if (extName.equalsIgnoreCase("xlsx")) {
                wb = new XSSFWorkbook(inputStream);
            } else if (extName.equalsIgnoreCase("xls")) {
                wb = new HSSFWorkbook(inputStream);
            } else {
                throw new BusinessException("999999","文件格式不正确，仅支持xls和xlsx格式");
            }
            Map<Integer,String> sheetNames = new HashMap<>();
            int sheetCount = ((Workbook) wb).getNumberOfSheets();
            for(int i=0;i<sheetCount;i++){
                sheetNames.put(i,((Workbook) wb).getSheetName(i));
            }
            return sheetNames;
        }catch (Exception e){
            logger.error("error", e);
            throw new BusinessException("999999","文件流读取失败：" + e.getMessage());
        }
    }

}
